package cn.ainit.sys.models;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.servlet.http.HttpSession;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.springframework.stereotype.Service;

import cn.ainit.common.ReturnObject;
import cn.ainit.sys.framework.FrameworkService;
import cn.ainit.sys.framework.entity.ComButtons;
import cn.ainit.sys.framework.entity.SysButtons;
import cn.ainit.sys.framework.entity.SysColumns;
import cn.ainit.sys.print.entity.SysPrint;
import cn.ainit.sys.query.datastore.DataStore;
@Service
public class SingleServiceImpl implements SingleService {
	private SingleDao singleDao;
	@Resource
	public void setSingleDao(SingleDao singleDao) {
		this.singleDao = singleDao;
	}


	private FrameworkService frameworkService;
	@Resource
	public void setFrameworkService(FrameworkService frameworkService) {
		this.frameworkService = frameworkService;
	}


	@Override
	public List butnList(String butnId) throws Exception {
		return this.singleDao.butnList(butnId);
	}

	@Override
	public List butnListRightTop(String butnId, String dsId, String oId) throws Exception{
		return this.singleDao.butnListRightTop(butnId, dsId, oId);
	}

	@Override
	public List<ComButtons> combutnList(String butnId) throws Exception {
		return this.singleDao.combutnList(butnId);
	}
	@Override
	public DataStore getDataStore(String dsId) throws Exception {
		return this.singleDao.getDataStore(dsId);
	}
	@Override
	public List queryRowBySql(String sqlText) throws Exception {
		return this.singleDao.queryRowBySql(sqlText);
	}

	@Override
	public List<SysColumns> loadColumns(String colId, String type)
	throws Exception {
		return singleDao.loadColumns(colId,type);
	}

	@Override
	public List<SysColumns> findFksByDsid(String dsId) throws Exception {
		return singleDao.findFksByDsid(dsId);
	}
	@Override
	public List<Map<String, Object>> findPksByDsid(String dsId) throws Exception {
		return singleDao.findPksByDsid(dsId);
	}
	@Override
	public List loadDzInfo(String dsId) throws Exception {
		return singleDao.loadDzInfo(dsId);
	}
	@Override
	public String formatSql(String dsId, String sqlText, String uid)
	throws Exception {
		return singleDao.formatSql(dsId, sqlText, uid);
	}

	@Override
	public String formatRowSql(String dsId, String sqlText, String uid)
	throws Exception {
		return singleDao.formatRowSql(dsId, sqlText, uid);
	}

	@Override
	public List<SysButtons> combutnListT(String dsid) throws Exception {
		return singleDao.combutnListT(dsid);
	}
	@Override
	public List<SysPrint> printTypeList(String dsId) throws Exception {
		return singleDao.printTypeList(dsId);
	}
	@Override
	public List combutnListRight(String butnId, String dsId,
			String oId) throws Exception {
		return singleDao.combutnListRight(butnId, dsId, oId);
	}
	@Override
	public List butnListRight(String butnId, String dsId, String oId,String flag)
	throws Exception {
		return singleDao.butnListRight(butnId, dsId, oId,flag);
	}
	@Override
	public String dcExcel(String title, String url, String[] topName,
			String[] dbCellName, List<Map<String, String>> list)
	throws Exception {
		return singleDao.dcExcel(title, url, topName, dbCellName, list);
	}
	@Override
	public String formatTranSql(String dsId, String sqlText, String uid)
	throws Exception {
		return singleDao.formatTranSql(dsId, sqlText, uid);
	}


	@Override
	public String getReturnImproInfo(String flg,String dsid,String kvs,SingleParam sp) {
		List<Map<String, Object>> listmap=null;
		String Json = "";
		List<String>listname=new ArrayList<String>(); 
		try {
			String url=sp.getUrl();
			String tableName="bs_importexcel";
			List   clellName=new ArrayList();			
			clellName.add("H_ISBN");
			clellName.add("H_NAME");
			clellName.add("H_PRICE");
			clellName.add("H_DISCOUNT");
			clellName.add("H_AMOUNT");
			clellName.add("PP_DESC");
			//获取到拼接好的sql语句
			List<String> list=ImportExcel.importEmployeeByPoi(sp.getInputStram(),url,tableName,clellName);
			//把数据插入到数据库
			boolean bo=singleDao.getUpdateInfo(list);
			//			String porSql="begin  pk_fx.up_matchimportexcel  ('"+ImportExcel.h_id_match+"'); end ;";;
			kvs = kvs.substring(0,kvs.length()-1);
			kvs =kvs.replaceAll("`", "\"");
			Json = singleDao.getimpFuc(flg,dsid,kvs,ImportExcel.h_id_match );
			System.out.println("参数："+flg+"#"+dsid+"#"+kvs+"#"+ImportExcel.h_id_match);
			System.out.println("数据："+Json);
			//			if(singleDao.getUpdateInfo(porSql)){
			//				String sql="select * from bs_importexcel where syspici='"+ImportExcel.h_id_match+"'";
			//				listmap= singleDao.findBySql(sql);
			//				Map<String,Object> map = new HashMap<String,Object>();
			//		 
			//				 
			//			}
		} catch (Exception e) {
			System.out.println(e);
		}
		return Json;
	}

	@Override
	public String getReturnImproInfoZT(String flg,String dsid,String kvs,SingleParam sp) {
		List<Map<String, Object>> listmap=null;
		String Json = "";
		List<String>listname=new ArrayList<String>();
		try {
			String url=sp.getUrl();
			String tableName="bs_importexcel";
			List   clellName=new ArrayList();
			clellName.add("H_ISBN");
			clellName.add("H_NAME");
			clellName.add("H_PRICE");
			clellName.add("ZT_AMOUNT");
			clellName.add("PX_CODE");
			clellName.add("TH_CODE");
			//获取到拼接好的sql语句
			List<String> list=ImportExcel.importEmployeeByPoiZT(sp.getInputStram(),url,tableName,clellName);
			//把数据插入到数据库
			boolean bo=singleDao.getUpdateInfo(list);
			kvs = kvs.substring(0,kvs.length()-1);
			kvs =kvs.replaceAll("`", "\"");
			Json = singleDao.getimpFucZT(flg,dsid,kvs,ImportExcel.h_id_match );
			System.out.println("参数："+flg+"#"+dsid+"#"+kvs+"#"+ImportExcel.h_id_match);
			System.out.println("数据："+Json);
		} catch (Exception e) {
			System.out.println(e);
		}
		return Json;
	}

	@Override
	public String getReturnImproInfoCYXH(String flg,String dsid,String kvs,SingleParam sp) {
		List<Map<String, Object>> listmap=null;
		String Json = "";
		List<String>listname=new ArrayList<String>();
		try {
			String url=sp.getUrl();
			String tableName="bs_importexcel";
			List   clellName=new ArrayList();
			clellName.add("H_ISBN");
			clellName.add("H_NAME");
			clellName.add("H_PRICE");
			clellName.add("CY_AMOUNT");
			//获取到拼接好的sql语句
			List<String> list=ImportExcel.importEmployeeByPoiCYXH(sp.getInputStram(),url,tableName,clellName);
			//把数据插入到数据库
			boolean bo=singleDao.getUpdateInfo(list);
			kvs = kvs.substring(0,kvs.length()-1);
			kvs =kvs.replaceAll("`", "\"");
			Json = singleDao.getimpFucCYXH(flg,dsid,kvs,ImportExcel.h_id_match );
			System.out.println("参数："+flg+"#"+dsid+"#"+kvs+"#"+ImportExcel.h_id_match);
			System.out.println("数据："+Json);
		} catch (Exception e) {
			System.out.println(e);
		}
		return Json;
	}
	
	@Override
	public String getReturnImproInfoCw(String flg,String dsid,String kvs,SingleParam sp) {
		List<Map<String, Object>> listmap=null;
		String Json = "";
		List<String>listname=new ArrayList<String>(); 
		try {
			String url=sp.getUrl();
			String tableName="CW_BS_IMPORTEXCEL";
			List   clellName=new ArrayList();
			clellName.add("H_ISBN");
			clellName.add("H_NAME");
			clellName.add("H_AMOUNT");
			clellName.add("AMOUNT");
			clellName.add("H_PRICE");
			//获取到拼接好的sql语句
			List<String> list=ImportExcel.importEmployeeByPoiCw(sp.getInputStram(),url,tableName,clellName);
			//把数据插入到数据库
			boolean bo=singleDao.getUpdateInfo(list);
		    System.out.println(bo);
			//			String porSql="begin  pk_fx.up_matchimportexcel  ('"+ImportExcel.h_id_match+"'); end ;";;
			kvs = kvs.substring(0,kvs.length()-1);
			kvs =kvs.replaceAll("`", "\"");
			Json = singleDao.getimpFucCw(flg,dsid,kvs,ImportExcel.h_id_match );
			System.out.println("参数："+flg+"#"+dsid+"#"+kvs+"#"+ImportExcel.h_id_match);
			System.out.println("数据："+Json);
			//			if(singleDao.getUpdateInfo(porSql)){
			//				String sql="select * from bs_importexcel where syspici='"+ImportExcel.h_id_match+"'";
			//				listmap= singleDao.findBySql(sql);
			//				Map<String,Object> map = new HashMap<String,Object>();
			//		 
			//				 
			//			}
		} catch (Exception e) {
			System.out.println(e);
		}
		return Json;
	}
	
	@Override
	public String getReturnImproInfoFee(String flg, String dsid, String kvs,
			SingleParam sp) {
		List<Map<String, Object>> listmap=null;
		String Json = "";
		List<String>listname=new ArrayList<String>(); 
		try {
			String url=sp.getUrl();
			String tableName="CW_BS_IMPORTEXCEL";
			List   clellName=new ArrayList();
			clellName.add("H_ISBN");
			clellName.add("H_NAME");
            clellName.add("H_PRICE");
			clellName.add("H_AMOUNT");
			clellName.add("AMOUNT");
			clellName.add("FEE_AMOUNT");
			clellName.add("FEE_ACTIVITY");
			//获取到拼接好的sql语句
			List<String> list=ImportExcel.importEmployeeByPoiFee(sp.getInputStram(),url,tableName,clellName);
			//把数据插入到数据库
			boolean bo=singleDao.getUpdateInfo(list);
		    System.out.println("bo="+bo);
			//			String porSql="begin  pk_fx.up_matchimportexcel  ('"+ImportExcel.h_id_match+"'); end ;";;
			kvs = kvs.substring(0,kvs.length()-1);
			kvs =kvs.replaceAll("`", "\"");
			Json = singleDao.getimpFucFee(flg,dsid,kvs,ImportExcel.h_id_match );
			System.out.println("参数："+flg+"#"+dsid+"#"+kvs+"#"+ImportExcel.h_id_match);
			System.out.println("数据："+Json);
			//			if(singleDao.getUpdateInfo(porSql)){
			//				String sql="select * from bs_importexcel where syspici='"+ImportExcel.h_id_match+"'";
			//				listmap= singleDao.findBySql(sql);
			//				Map<String,Object> map = new HashMap<String,Object>();
			//		 
			//				 
			//			}
		} catch (Exception e) {
			System.out.println(e);
		}
		return Json;
	}
	/*******************************************
	 * 2015年11月26日17:46:30 刘振涛
	 * @param sp
	 * @param dsid
	 * @return
	 *******************************************/
	public String ImpExcelToDbAndProc(SingleParam sp,String dsid,String uid){
		try {
			List<String> sql=new ArrayList();
			String out=ImportExcel.ImpExcelToDbAndProc(sp.getInputStram(), sp.getUrl(),dsid,sql);//最后一个参数接收sql语句
			String pici="";
			if(out.indexOf("pici")==0){
				pici=out.split(":")[1];
			}
			if(pici.equals("")) return out;
			singleDao.getUpdateInfo(sql);
			return singleDao.CommonImpProc(dsid, pici, uid);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return e.getMessage();
		}
	}

	@Override
	public List<Map<String, Object>> getReturnImpro(SingleParam sp) {
		List<Map<String, Object>> listmap=null;
		try {
			String sqlColoum =" select * from sys_columns where ds_id='"+sp.getDsid()+"' and hidden_qr is null order by  ordernum ";
			List<Map<String, Object>> maps=	singleDao.findBySql(sqlColoum);
			List lists =new ArrayList();
			for(int i =0 ;i<maps.size();i++){
				lists.add(maps.get(i).get("NAME").toString());
			}
			String url=sp.getUrl();
			String tableName="bs_importexcel";
			//	String [] clellName={"H_ISBN","H_NAME","H_PRICE","H_AMOUNT","H_DISCOUNT"};

			//获取到拼接好的sql语句
			List<String> list=new ArrayList<String>();
			String pici=null;//插入批次
			System.out.println(sp.getDsid());
			if ("fx_xskc".equals(sp.getDsid())||"fx_xshd".equals(sp.getDsid())) {
				pici=ImportExcel.ImpExcelToDbAndProc(sp.getInputStram(),sp.getFile().getOriginalFilename(), sp.getDsid(), list);
			}else{
				list=ImportExcel.importEmployeeByPoi(sp.getInputStram(),url,tableName,lists);
			}
			
			//把数据插入到数据库
			boolean bo=singleDao.getUpdateInfo(list);	
			if ("fx_xskc".equals(sp.getDsid())||"fx_xshd".equals(sp.getDsid())) {
				String porSql="begin   pk_fx.UP_BS_IMP_COM('"+pici.replaceAll("pici:","")+"'); end ;";
				if(singleDao.getUpdateInfo_s(porSql)){
				String sql="select * from bs_imp_com where pici='"+pici.replaceAll("pici:","")+"'";
				listmap= singleDao.findBySql(sql);
				List<Map<String, Object>> listmap2=new ArrayList<Map<String,Object>>();
				for (int i = 0; i < listmap.size(); i++) {
					Map<String ,Object>map=new HashMap<String, Object>();	
					JSONObject objjson = JSONObject. fromObject(listmap.get(i).get("CONTENT") );
					JSONObject objjson2 = JSONObject. fromObject(listmap.get(i));
					map.put("H_ISBN",objjson.get("书号"));
					map.put("H_NAME",objjson.get("书名"));
					
					map.put("H_PRICE",objjson.get("定价"));
					map.put("H_AMOUNT",objjson.get("数量"));
					map.put("H_DISCOUNT",objjson.get("折扣"));
                    map.put("IS_PP", objjson2.get("IS_PP"));
               
                    	map.put("SYSPICI", pici.replaceAll("pici:",""));
                    	map.put("H_ID",listmap.get(i).get("H_ID"));
					listmap2.add(map);
				}
               listmap=listmap2;
				}
			}else{
				String porSql="begin   pk_fx.up_matchimportexcel('"+ImportExcel.h_id_match+"'); end ;";;
				if(singleDao.getUpdateInfo_s(porSql)){
					String sql="select CONTENT from bs_importexcel where syspici='"+ImportExcel.h_id_match+"'";
					listmap= singleDao.findBySql(sql);
				}

			}


		} catch (Exception e) {
			System.out.println(e);
		}
		return listmap;
	}
	@Override
	public List getReturnImproKH(SingleParam sp) {
		List<Map<String, Object>> listmap=null;
		try {
			String sqlColoum =" select * from sys_columns where ds_id='"+sp.getDsid()+"' and nvl(hidden_qr,'F')='F' order by  ordernum ";
			List<Map<String, Object>> maps=	singleDao.findBySql(sqlColoum);
			List lists =new ArrayList();
			for(int i =0 ;i<maps.size();i++){
				System.out.println(maps.get(i).get("NAME").toString());
				lists.add(maps.get(i).get("NAME").toString());
			}
			String url=sp.getUrl();
			String tableName="bs_impexcelKH";
			//	String [] clellName={"H_ISBN","H_NAME","H_PRICE","H_AMOUNT","H_DISCOUNT"};

			//获取到拼接好的sql语句
			List<String> list=ImportExcel.importEmployeeByPoi(sp.getInputStram(),url,tableName,lists);
			//把数据插入到数据库
			boolean bo=singleDao.getUpdateInfo(list);
			
			String porSql="begin   pk_fx.UP_MATCHIMPORTEXCELKH('"+ImportExcel.h_id_match+"'); end ;";
			if(singleDao.getUpdateInfo_s(porSql)){
				String sql="select * from bs_impexcelKH where syspici='"+ImportExcel.h_id_match+"' order by is_pp ";
				listmap= singleDao.findBySql(sql);
			}
		} catch (Exception e) {
			System.out.println(e);
		}
		return listmap;
	}




	@Override
	public List<Map<String, Object>> getOrerInfo(SingleParam sp) {
		String sql ="select * from bs_product where h_isbn='"+sp.getHisbn()+"'";
		return singleDao.findBySql(sql);
	}
	@Override
	public List<Map<String, Object>> getOrderKHInfo(SingleParam sp) {
		String sql ="SELECT * FROM bs_clients s WHERE s.clients_name = '"+sp.getCname()+"'";
		return singleDao.findBySql(sql);
	}
	@Override
	public boolean getDelMateOrder(SingleParam sp) {
		String sql ="delete bs_importexcel where syspici='"+sp.getSyspici()+"'";
		return singleDao.getUpdateInfo_s(sql);

	}
	@Override
	public boolean getDelMateOrder2(SingleParam sp) {
		String sql ="delete bs_imp_com where pici='"+sp.getSyspici()+"'";
		return singleDao.getUpdateInfo_s(sql);

	}
	@Override
	public boolean getDelKHDaat(SingleParam sp) {
		String sql ="delete bs_impexcelKH where syspici='"+sp.getSyspici()+"'";
		return singleDao.getUpdateInfo_s(sql);
	}
	@Override
	public String dbclickRight(String dsId, String pkVal, String manId)
	throws Exception {
		return this.singleDao.dbclickRight(dsId, pkVal, manId);
	}
	@Override
	public List getClientById(String cId) {
		String sql = "select * from bs_clients s where s.clients_id = '"+cId+"'";
		return singleDao.findBySql(sql);
	}
	@Override
	public List returnNCopyList(String mxDsId) throws Exception {
		return singleDao.returnNCopyList(mxDsId);
	}

	@Override
	public List<Map<String, Object>> queryProduct(String H_ISBN, String H_NAME) {
		return singleDao.queryProduct(H_ISBN, H_NAME);
	}
	@Override
	public ReturnObject setFxPl(String pkName, String zDsId, String dsId,
			String djId, String plFlag, JSONObject plObj, JSONArray plRows,
			HttpSession session) throws Exception {
		DataStore daStore=frameworkService.getDataStoreById(dsId);
		return singleDao.setFxPl(pkName, zDsId, daStore, dsId, djId, plFlag, plObj, plRows, session) ;
	}

    /*******************************************
     * 2015年11月26日17:46:30 lhl
     * @param sp
     * @param dsid
     * @return
     *******************************************/
    @Override
    public int sureSaveisnull(String NAME,String ccid) {
        int r= singleDao.sureSaveisnull(NAME, ccid);
        return r;
    }

	@Override
	public int cwygrkSaveisnull(String H_ID,String YGR_ID) {
		int r= singleDao.cwygrkSaveisnull(H_ID,YGR_ID);
		return r;
	}




    /*
    * 客户实销上报（按日）
    * */
    @Override
    public String getReturnImproInfofc(String flg,String dsid,String kvs,SingleParam sp) {
        List<Map<String, Object>> listmap=null;
        String Json = "";
        List<String>listname=new ArrayList<String>();
        try {
            String url=sp.getUrl();
            String tableName="FX_SALES_COM";
            List   clellName=new ArrayList();
            clellName.add("H_ISBN");
            clellName.add("H_NAME");
            clellName.add("H_PRICE");
            clellName.add("H_AMOUNT");
            clellName.add("AMOUNT");
//            clellName.add("H_TAOZHUANG");
//            clellName.add("C_CODE");
            //获取到拼接好的sql语句
            List<String> list=ImportExcel.importEmployeeByPoiFC(sp.getInputStram(), url, tableName, clellName);
            //把数据插入到数据库
            boolean bo=singleDao.getUpdateInfo(list);
            System.out.println("bo="+bo);
            //			String porSql="begin  pk_fx.up_matchimportexcel  ('"+ImportExcel.h_id_match+"'); end ;";;
            kvs = kvs.substring(0,kvs.length()-1);
            kvs =kvs.replaceAll("`", "\"");
            Json = singleDao.getimpFucFC(flg, dsid, kvs, ImportExcel.h_id_match);
            System.out.println("参数："+flg+"#"+dsid+"#"+kvs+"#"+ImportExcel.h_id_match);
            System.out.println("数据："+Json);
            //			if(singleDao.getUpdateInfo(porSql)){
            //				String sql="select * from bs_importexcel where syspici='"+ImportExcel.h_id_match+"'";
            //				listmap= singleDao.findBySql(sql);
            //				Map<String,Object> map = new HashMap<String,Object>();
            //
            //
            //			}
        } catch (Exception e) {
            System.out.println(e);
        }
        return Json;
    }
    @Override
    public boolean getDelMateOrders(SingleParam sp) {
        String sql ="delete FX_SALES_COM where PKID='"+sp.getSyspici()+"'";
        return singleDao.getUpdateInfo_s(sql);

    }
    @Override
    public List<Map<String, Object>> queryProductBYDAY(String H_ISBN, String H_NAME) {
        return singleDao.queryProductBYDAY(H_ISBN, H_NAME);
    }


    /*
    * 客户实销上报（按月）
    * */
    @Override
    public String getReturnImproInfofcByMonth(String flg,String dsid,String kvs,SingleParam sp) {
        List<Map<String, Object>> listmap=null;
        String Json = "";
        List<String>listname=new ArrayList<String>();
        try {
            String url=sp.getUrl();
            String tableName="FX_KHSX_IMPORTEXCEL";
            List   clellName=new ArrayList();
            clellName.add("H_ISBN");
            clellName.add("H_NAME");
            clellName.add("H_PRICE");
            clellName.add("H_AMOUNT");
            clellName.add("XS_MONEY");
            clellName.add("AMOUNT");
            clellName.add("H_DISCOUNT");
            clellName.add("XS_PRICE");
            //获取到拼接好的sql语句
            List<String> list=ImportExcel.importEmployeeByPoiFC(sp.getInputStram(), url, tableName, clellName);
            //把数据插入到数据库
            boolean bo=singleDao.getUpdateInfo(list);
            System.out.println("bo="+bo);
            //			String porSql="begin  pk_fx.up_matchimportexcel  ('"+ImportExcel.h_id_match+"'); end ;";;
            kvs = kvs.substring(0,kvs.length()-1);
            kvs =kvs.replaceAll("`", "\"");
            Json = singleDao.getimpFucFCByMonth(flg, dsid, kvs, ImportExcel.h_id_match);
            System.out.println("参数："+flg+"#"+dsid+"#"+kvs+"#"+ImportExcel.h_id_match);
            System.out.println("数据："+Json);
            //			if(singleDao.getUpdateInfo(porSql)){
            //				String sql="select * from bs_importexcel where syspici='"+ImportExcel.h_id_match+"'";
            //				listmap= singleDao.findBySql(sql);
            //				Map<String,Object> map = new HashMap<String,Object>();
            //
            //
            //			}
        } catch (Exception e) {
            System.out.println(e);
        }
        return Json;
    }

    @Override
    public boolean delsmataOrdersfms(SingleParam sp) {
        String sql ="delete FX_KHSX_IMPORTEXCEL where pici='"+sp.getSyspici()+"'";
        return singleDao.getUpdateInfo_s(sql);

    }

}
